package DBLayer;
/*
 * DataAccessHelper provides methods to talk to Postgres data store
 * It can be further customized to talk to any RDBMS that is supported
 * using JDBC
 */

import java.sql.*;
import java.util.ArrayList;

public class DBConnection {
	
	/*
	 * Method to execute a SELECT kind of query
	 */
	@SuppressWarnings("finally")
	public static ResultSet executeQuery(String sql,Connection conn)
	{
		ResultSet rs = null;
		
		try{
			Statement stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
		}
		catch (SQLException sqlEx){
			System.out.println(sqlEx.getMessage());
		}
		catch (Exception ex){
			System.out.println(ex.getMessage());
		}
		finally{
            return rs;
		}	
	}
    @SuppressWarnings("finally")
	public static boolean excuteUpdate(String query,Connection conn){
        try{
            
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(query);
        }
        catch(SQLException sqlex){
            System.out.println(sqlex.getMessage());
        }
        finally{
            return true;
        }
    }
	
	/*
	 * Method to execute DML statements
	 */
	public static int execute(String sql,ArrayList<Object> params,ArrayList<String> paramTypes)
	{
		Connection conn = getConnection();
		int recordsAffected = 0;
		
		try
		{
			PreparedStatement stmt = conn.prepareStatement(sql);
            setParameters(stmt, params, paramTypes);
			recordsAffected = stmt.executeUpdate();
		}
		catch (SQLException sqlEx)
		{
			System.out.println(sqlEx.toString());
		}
		finally
		{
			try {
				conn.close();
			}
			catch(Exception ex)
			{
				
			}
		}
		return recordsAffected;
	}
        
    private static void setParameters(PreparedStatement stmt, ArrayList<Object> params,ArrayList<String> paramTypes)
        throws SQLException
    {

        for(int i=0; i < params.size(); i++){
            if(paramTypes.get(i).equals("String")){
                stmt.setString(i+1, (String) params.get(i));
            }
            else if (paramTypes.get(i).equals("Integer")) {
                stmt.setInt(i+1, (Integer) params.get(i));
            }
            else if (paramTypes.get(i).equals("Double")){
                stmt.setDouble(i+1, (Double) params.get(i));
            }
            else if (paramTypes.get(i).equals("Boolean")){
                stmt.setBoolean(i+1, (Boolean) params.get(i));
            }
        }
    }
	
	/*
	 * Method to establish connection with the database
	 */
	public static Connection getConnection()
	{
		Connection conn = null;
		
		try
		{
			
			//Class.forName("com.mysql.jdbc.Driver");
			//conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/portalDatabase","root","root");			
			
			try {
				Class.forName("com.mysql.jdbc.Driver").newInstance();
				conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/portalDatabase", "root", "root");
			} catch (InstantiationException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (IllegalAccessException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
		}
		catch (ClassNotFoundException cnfEx)
		{	
                    //System.out.println(cnfEx.getMessage());
			cnfEx.printStackTrace();
		}
		catch (SQLException sqlEx)
		{		
                    //System.out.println(sqlEx.getMessage());
			sqlEx.printStackTrace();
		}
		
		return conn;
	}

}
